﻿using QJY.API;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Web;
using FastReflectionLib;
using System.Data;
using QJY.Data;
using Newtonsoft.Json;

namespace QJY.API
{
    public class SJTJManage : IWsService
    {

        public void ProcessRequest(HttpContext context, ref Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            MethodInfo methodInfo = typeof(SJTJManage).GetMethod(msg.Action.ToUpper());
            SJTJManage model = new SJTJManage();
            methodInfo.FastInvoke(model, new object[] { context, msg, P1, P2, UserInfo });
        }




        /// <summary>
        /// 支出日报
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void ZCJHZXRB(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2);
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");


            string strSQL = string.Format(" SELECT CASE WHEN GROUPING(ppath)=1 THEN '合计' ELSE ppath END AS ppath,CASE WHEN GROUPING(ppath)=0 AND GROUPING(dfdw)=1 THEN '小计' ELSE dfdw END AS dfdw ,SUM(A.ZXJE) as DRZXJE,SUM(VwZC.zxje) AS BYZXJE,SUM(VwZC.JE1)  AS BYJHJE,'' AS BYWFJE  FROM ( SELECT SZID,SUM(JSJE) AS ZXJE FROM SZHL_JH_ITEMZX WHERE JHID='{0}' AND SZTYPE='1' AND szdate BETWEEN '{1} 00:00:00' AND  '{1} 23:59:59' GROUP BY SZID  ) AS A INNER JOIN VwZC  ON A.SZID=VwZC.ID ", JHId, strSDate);
            if (strdwids != "")
            {
                strSQL = strSQL + " where dwid in ('" + strdwids.ToFormatLike(',') + "')";
            }
            strSQL = strSQL + "GROUP BY  ppath,dfdw with rollup";
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSQL);
            dt.Columns.Add("hide");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //dt.Rows[i]["BYJHJE"] = new SZHL_JH_ITEMZXB().GetDYJHJE(JHId, dt.Rows[i]["dfdw"].ToString(), dt.Rows[i]["ppath"].ToString());
                //dt.Rows[i]["BYZXJE"] = new SZHL_JH_ITEMZXB().GetDYZXJE(JHId, dt.Rows[i]["dfdw"].ToString(), dt.Rows[i]["ppath"].ToString());
                string strTempPath = dt.Rows[i]["ppath"].ToString();
                if (strTempPath != "合计")
                {
                    dt.Rows[i]["ppath"] = new SZHL_JH_FLB().GetEntity(D => D.path == strTempPath).name;
                }
                if (i > 0)
                {
                    if (dt.Rows[i]["ppath"].ToString() == dt.Rows[i - 1]["ppath"].ToString())
                    {
                        dt.Rows[i]["hide"] = "Y";
                    }
                }
                dt.Rows[i]["BYWFJE"] = decimal.Parse(dt.Rows[i]["BYJHJE"].ToString()) - decimal.Parse(dt.Rows[i]["BYZXJE"].ToString());


            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["hide"].ToString() == "Y")
                {
                    dt.Rows[i]["ppath"] = "";
                }
            }
            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, UserInfo.UserBMQXCode);

        }


        public void EXPORTZX(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            string DC = context.Request["DC"] ?? "";
            DataTable dt = new DataTable();
            if (DC == "ZCJHZXRB")
            {
                ZCJHZXRB(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string sqlCol = "ppath|款项性质,dfdw|对方单位名称,DRZXJE|今日支付额,BYJHJE|本月计划额度,BYZXJE|本月已付,BYWFJE|未付额";
                dt.DelTableCol(sqlCol);
            }
            if (DC == "ZJSRSXB")
            {
                ZJSRSXB(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string sqlCol = "szdate|收支时间,Remark1|项目期别,FLName|计划科目,hklxname|还款类型名称,cplxname|产品类型,jsje|金额,jsfs|结算方式,jrjg|金融机构,pjh|票据号,remark|备注,CRUser|操作员";
                dt.DelTableCol(sqlCol);
            }
            if (DC == "ZJZCSXB")
            {
                ZJZCSXB(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string sqlCol = "szdate|收支时间,Remark1|项目期别,FLName|计划科目,htbh|合同编号,htnr|合同内容,dfdw|对方单位,jsje|金额,jsfs|结算方式,remark|备注,CRUser|操作员";
                dt.DelTableCol(sqlCol);
            }
            if (DC == "ZJRB")
            {
                ZJRB(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string sqlCol = "SZTYPE|数据类型,FLName|计划科目,DRJE|合同编号,DYJE|合同内容";
                dt.DelTableCol(sqlCol);
            }
            if (DC == "XJLCSB")
            {
                XJLCSB(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string strdwnames = msg.Result1;
                string strdwid = msg.Result2;
                string strDC = "";
                for (int i = 0; i < strdwid.Split(',').Count(); i++)
                {

                    strDC = strDC + strdwid.Split(',')[i] + "|" + strdwnames.Split(',')[i] + ",";
               
                }
                string sqlCol = "name|科目,"+ strDC.Trim(',') + ",合计|合计";
                dt.DelTableCol(sqlCol);
            }
            if (DC == "LYFX")
            {
                LYFX(context, msg, P1, P2, UserInfo);
                dt = msg.Result;
                string sqlCol = "dwname|单位名称,Ppath|计划科目,SYWWC|上月未完成,DRLS|导入历史计划,BYXZ|本月新增,BYJH|本月计划";
                dt.DelTableCol(sqlCol);
            }


            


            CommonHelp ch = new CommonHelp();
            msg.ErrorMsg = ch.ExportToExcel("执行", dt);
        }


        /// <summary>
        /// 资金收入时序表
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void ZJSRSXB(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2); ;
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            string strEDate = context.Request["edate"] ?? DateTime.Now.ToString("yyyy-MM-dd");


            string strSQL = string.Format("SELECT VwSR.dwname, JH_Auth_User.UserRealName, SZHL_JH_ITEMZX.szdate,VwSR.Remark1,FLName,hklxname,cplxname,SZHL_JH_ITEMZX.jsje,SZHL_JH_ITEMZX.jsfs,SZHL_JH_ITEMZX.jrjg ,SZHL_JH_ITEMZX.pjh,SZHL_JH_ITEMZX.remark,SZHL_JH_ITEMZX.CRUser FROM SZHL_JH_ITEMZX INNER JOIN VwSR ON SZHL_JH_ITEMZX.szid=VwSR.id INNER JOIN JH_Auth_User on SZHL_JH_ITEMZX.CRUser=JH_Auth_User.UserName where sztype='0' AND SZHL_JH_ITEMZX.JHID='{0}' AND SZHL_JH_ITEMZX.szdate BETWEEN '{1} 00:00:00' AND  '{2} 23:59:59' ", JHId, strSDate, strEDate);
            if (strdwids != "")
            {
                strSQL = strSQL + " and VwSR.dwid in ('" + strdwids.ToFormatLike(',') + "')";
            }
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSQL);
            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, UserInfo.UserBMQXCode);


        }


        /// <summary>
        /// 资金支出时序表
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void ZJZCSXB(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2); ;
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            string strEDate = context.Request["edate"] ?? DateTime.Now.ToString("yyyy-MM-dd");

            string strSQL = string.Format("SELECT VwZC.dwname, JH_Auth_User.UserRealName, SZHL_JH_ITEMZX.szdate,VwZC.Remark1, VwZC.htbh,VwZC.htnr,VwZC.dfdw, FLName,SZHL_JH_ITEMZX.jsje,SZHL_JH_ITEMZX.jsfs,SZHL_JH_ITEMZX.jrjg ,SZHL_JH_ITEMZX.pjh,SZHL_JH_ITEMZX.remark,SZHL_JH_ITEMZX.CRUser FROM SZHL_JH_ITEMZX INNER JOIN VwZC ON SZHL_JH_ITEMZX.szid=VwZC.id INNER JOIN JH_Auth_User on SZHL_JH_ITEMZX.CRUser=JH_Auth_User.UserName  where sztype='1' AND SZHL_JH_ITEMZX.JHID='{0}' AND SZHL_JH_ITEMZX.szdate BETWEEN '{1} 00:00:00' AND  '{2} 23:59:59' ", JHId, strSDate, strEDate);
            if (strdwids != "")
            {
                strSQL = strSQL + " and VwZC.dwid in ('" + strdwids.ToFormatLike(',') + "')";
            }
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSQL);
            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, UserInfo.UserBMQXCode);


        }



        /// <summary>
        /// 资金日报
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void ZJRB(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2);
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");

            string strSRSQL = string.Format("SELECT '收入' as SZTYPE, CASE WHEN GROUPING(FLName)=1 THEN '合计' ELSE FLName END AS FLName,SUM(DRJE) AS DRJE,SUM(ZXJE) AS DYJE FROM ( SELECT szid,SUM(jsje) as DRJE FROM SZHL_JH_ITEMZX  where sztype='0' AND SZHL_JH_ITEMZX.JHID='{0}' AND SZHL_JH_ITEMZX.szdate BETWEEN '{1} 00:00:00' AND  '{1} 23:59:59' GROUP BY szid ) A  INNER JOIN VwSR ON A.szid=VwSR.id WHERE  dwid in (" + strdwids + ")   GROUP BY FLName with rollup", JHId, strSDate);
            string strZCSQL = string.Format("SELECT '支出' as SZTYPE,  CASE WHEN GROUPING(ppath)=1 THEN '合计' ELSE ppath END AS ppath,SUM(DRJE) AS DRJE,SUM(ZXJE) AS DYJE FROM ( SELECT szid,SUM(jsje) as DRJE FROM SZHL_JH_ITEMZX  where sztype='1' AND SZHL_JH_ITEMZX.JHID='{0}' AND SZHL_JH_ITEMZX.szdate BETWEEN '{1} 00:00:00' AND  '{1} 23:59:59' GROUP BY szid ) A  INNER JOIN VwZC ON A.szid=VwZC.id  WHERE  dwid in (" + strdwids + ")  GROUP BY Ppath with rollup", JHId, strSDate);
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSRSQL + " UNION ALL " + strZCSQL);
            dt.Columns.Add("hide");

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                string strTempPath = dt.Rows[i]["SZTYPE"].ToString();
                string strFLName = dt.Rows[i]["FLName"].ToString();

                if (strTempPath == "支出" && strFLName != "合计")
                {
                    dt.Rows[i]["FLName"] = new SZHL_JH_FLB().GetEntity(D => D.path == strFLName).name;
                }
                if (i > 0)
                {
                    if (dt.Rows[i]["SZTYPE"].ToString() == dt.Rows[i - 1]["SZTYPE"].ToString())
                    {
                        dt.Rows[i]["hide"] = "Y";
                    }
                }

            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i]["hide"].ToString() == "Y")
                {
                    dt.Rows[i]["SZTYPE"] = "";
                }
            }


            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, UserInfo.UserBMQXCode);


        }

        /// <summary>
        /// 现金流测算表
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void XJLCSB(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2); ;
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            string strEDate = context.Request["edate"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            string strSQL = string.Format("SELECT FLType,PID,path,name FROM SZHL_JH_FL ORDER BY FLType DESC, path ");
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSQL);
            string strDWName = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, strdwids);
            List<string> LISTDW = new List<string>();
            foreach (var dwid in strdwids.Split(','))
            {
                if (dwid != "1728")
                {
                    dt.Columns.Add(dwid);
                    LISTDW.Add(dwid);
                }
            }
            dt.Columns.Add("合计");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["合计"] = "0";
                foreach (var dwid in LISTDW)
                {
                    dt.Rows[i][dwid] = new SZHL_JH_ITEMZXB().GetDYJHJE(JHId, dwid, dt.Rows[i]["path"].ToString(), dt.Rows[i]["FLType"].ToString());
                    dt.Rows[i]["合计"] = (decimal.Parse(dt.Rows[i]["合计"].ToString()) + decimal.Parse(dt.Rows[i][dwid].ToString())).ToString();
                }
                int strNameLen = dt.Rows[i]["path"].ToString().Length;
                string strLen = "";
                for (int m = 0; m < strNameLen; m++)
                {
                    strLen = strLen + "-";
                }
                dt.Rows[i]["name"] = strLen + dt.Rows[i]["name"].ToString();
            }
            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, LISTDW.ListTOString(','));
            msg.Result2 =  LISTDW.ListTOString(',');


        }


        /// <summary>
        /// 提报计划来源分析(只抓取审核中以及审核通过的状态)
        /// </summary>
        /// <param name="context"></param>
        /// <param name="msg"></param>
        /// <param name="P1"></param>
        /// <param name="P2"></param>
        /// <param name="UserInfo"></param>
        public void LYFX(HttpContext context, Msg_Result msg, string P1, string P2, JH_Auth_UserB.UserInfo UserInfo)
        {
            int JHId = int.Parse(P1);
            string strdwids = (P2 == "" ? UserInfo.UserBMQXCode : P2); ;
            string strSDate = context.Request["sdate"] ?? DateTime.Now.ToString("yyyy-MM-dd");
            string strEDate = context.Request["edate"] ?? DateTime.Now.ToString("yyyy-MM-dd");

            string strSQL = string.Format("SELECT dwid,dwname, Ppath,SUM(je1) as BYJH, '0' AS SYWWC, '0' AS DRLS, '0' AS BYXZ  FROM VwZC WHERE  status NOT IN ('0','-1')   and  JHID='{0}'  AND  VwZC.dwid in ('{1}')  GROUP BY dwid,dwname, Ppath ORDER BY dwname  ", JHId, strdwids.ToFormatLike(','));
      
            DataTable dt = new SZHL_JH_ITEMZCB().GetDTByCommand(strSQL);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["DRLS"] = new SZHL_JH_ITEMZXB().GetDYJHJE(JHId, dt.Rows[i]["dwid"].ToString(), dt.Rows[i]["Ppath"].ToString());
                dt.Rows[i]["BYXZ"] = new SZHL_JH_ITEMZXB().GetXZJHJE(JHId, dt.Rows[i]["dwid"].ToString(), dt.Rows[i]["Ppath"].ToString());
                dt.Rows[i]["BYXZ"] = new SZHL_JH_ITEMZXB().GetSYWWCJE(JHId, dt.Rows[i]["dwid"].ToString(), dt.Rows[i]["Ppath"].ToString());

                string strTempPath = dt.Rows[i]["ppath"].ToString();
                dt.Rows[i]["ppath"] = new SZHL_JH_FLB().GetEntity(D => D.path == strTempPath).name;

            }
            msg.Result = dt;
            msg.Result1 = new JH_Auth_BranchB().GetBranchName(UserInfo.User.ComId.Value, UserInfo.UserBMQXCode);


        }

    }
}